<!DOCTYPE html>
<html lang="zh-CN">





<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/michuanblog/img/favicon.png">
  <link rel="icon" type="image/png" href="/michuanblog/img/favicon.png">
  <meta name="viewport"
        content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="description" content="Coding">
  <meta name="author" content="胡晗">
  <meta name="keywords" content="">
  <title>LeetCodede_DataBase - 胡晗&#39;s Blog</title>

  <link  rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.staticfile.org/github-markdown-css/4.0.0/github-markdown.min.css" />
  <link  rel="stylesheet" href="/michuanblog/lib/hint/hint.min.css" />

  
    <link  rel="stylesheet" href="https://cdn.staticfile.org/highlight.js/10.0.0/styles/github-gist.min.css" />
  

  


<!-- 主题依赖的图标库，不要自行修改 -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_yg9cfy8wd6.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_pjno9b9zyxs.css">


<link  rel="stylesheet" href="/michuanblog/css/main.css" />

<!-- 自定义样式保持在最底部 -->


  <script  src="/michuanblog/js/utils.js" ></script>
<meta name="generator" content="Hexo 4.2.1"></head>


<body>
  <header style="height: 70vh;">
    <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand"
       href="/michuanblog/">&nbsp;<strong>MiChuan</strong>&nbsp;</a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/michuanblog/">
                <i class="iconfont icon-home-fill"></i>
                首页
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/michuanblog/archives/">
                <i class="iconfont icon-archive-fill"></i>
                归档
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/michuanblog/categories/">
                <i class="iconfont icon-category-fill"></i>
                分类
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/michuanblog/tags/">
                <i class="iconfont icon-tags-fill"></i>
                标签
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/michuanblog/about/">
                <i class="iconfont icon-user-fill"></i>
                关于
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" data-toggle="modal" data-target="#modalSearch">&nbsp;&nbsp;<i
                class="iconfont icon-search"></i>&nbsp;&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

    <div class="view intro-2" id="background" parallax=true
         style="background: url('/michuanblog/img/default.png') no-repeat center center;
           background-size: cover;">
      <div class="full-bg-img">
        <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
          <div class="container text-center white-text fadeInUp">
            <span class="h2" id="subtitle">
              
            </span>

            
              
  <div class="mt-3 post-meta">
    <i class="iconfont icon-date-fill" aria-hidden="true"></i>
    <time datetime="2020-08-18 13:59">
      2020年8月18日 下午
    </time>
  </div>


<div class="mt-1">
  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      2.7k 字
    </span>
  

  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      
      38
       分钟
    </span>
  

  
  
</div>

            
          </div>

          
        </div>
      </div>
    </div>
  </header>

  <main>
    
      

<div class="container-fluid">
  <div class="row">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-md">
      <div class="container nopadding-md" id="board-ctn">
        <div class="py-5" id="board">
          <div class="post-content mx-auto" id="post">
            
            <article class="markdown-body">
              <h2 id="Leetcode-DataBase"><a href="#Leetcode-DataBase" class="headerlink" title="Leetcode DataBase"></a>Leetcode DataBase</h2><h3 id="简单题-12道"><a href="#简单题-12道" class="headerlink" title="简单题 12道"></a>简单题 12道</h3><h4 id="175-组合两个表"><a href="#175-组合两个表" class="headerlink" title="175 组合两个表"></a>175 组合两个表</h4><h5 id="题目"><a href="#题目" class="headerlink" title="题目"></a>题目</h5><p>表1: Person</p>
<pre><code class="hljs asciidoc"><span class="hljs-code">+-------------+</span>---------+
| 列名         | 类型     |
<span class="hljs-code">+-------------+</span>---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
<span class="hljs-code">+-------------+</span>---------+
PersonId 是上表主键</code></pre>
<p>表2: Address</p>
<pre><code class="hljs asciidoc"><span class="hljs-code">+-------------+</span>---------+
| 列名         | 类型    |
<span class="hljs-code">+-------------+</span>---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
<span class="hljs-code">+-------------+</span>---------+
AddressId 是上表主键</code></pre>
<p>编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：</p>
<pre><code class="hljs autohotkey"><span class="hljs-built_in">FirstName,</span> LastName, City, State</code></pre>
<h5 id="解析"><a href="#解析" class="headerlink" title="解析"></a>解析</h5><p>利用左连接进行组合查询</p>
<h5 id="代码"><a href="#代码" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">select</span> FirstName, LastName, City, State
<span class="hljs-keyword">from</span> Person <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> Address
<span class="hljs-keyword">on</span> Person.PersonId = Address.PersonId</code></pre>
<h4 id="176-第二高的薪水"><a href="#176-第二高的薪水" class="headerlink" title="176 第二高的薪水"></a>176 第二高的薪水</h4><h5 id="题目-1"><a href="#题目-1" class="headerlink" title="题目"></a>题目</h5><p>编写一个 SQL 查询，获取 <code>Employee</code> 表中第二高的薪水（Salary） 。</p>
<pre><code class="hljs sql">+<span class="hljs-comment">----+--------+</span>
| Id | Salary |
+<span class="hljs-comment">----+--------+</span>
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+<span class="hljs-comment">----+--------+</span></code></pre>
<h5 id="解析-1"><a href="#解析-1" class="headerlink" title="解析"></a>解析</h5><p>利用limit限制排序后获取数量，offset设置偏移量</p>
<h5 id="代码-1"><a href="#代码-1" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span>
    (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">DISTINCT</span>
            Salary
        <span class="hljs-keyword">FROM</span>
            Employee
        <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> Salary <span class="hljs-keyword">DESC</span>
        <span class="hljs-keyword">LIMIT</span> <span class="hljs-number">1</span> <span class="hljs-keyword">OFFSET</span> <span class="hljs-number">1</span>) <span class="hljs-keyword">AS</span> SecondHighestSalary
;</code></pre>
<h4 id="181-超过经理收入的员工"><a href="#181-超过经理收入的员工" class="headerlink" title="181 超过经理收入的员工"></a>181 超过经理收入的员工</h4><h5 id="题目-2"><a href="#题目-2" class="headerlink" title="题目"></a>题目</h5><p><code>Employee</code> 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。</p>
<pre><code class="hljs sql">+<span class="hljs-comment">----+-------+--------+-----------+</span>
| Id | Name  | Salary | ManagerId |
+<span class="hljs-comment">----+-------+--------+-----------+</span>
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+<span class="hljs-comment">----+-------+--------+-----------+</span></code></pre>
<p>给定 <code>Employee</code> 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。</p>
<h5 id="解析-2"><a href="#解析-2" class="headerlink" title="解析"></a>解析</h5><p>联合查询</p>
<h5 id="代码-2"><a href="#代码-2" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">select</span> b.Name <span class="hljs-keyword">as</span> Employee
<span class="hljs-keyword">from</span> Employee <span class="hljs-keyword">as</span> a, Employee <span class="hljs-keyword">as</span> b
<span class="hljs-keyword">where</span> a.Id = b.ManagerId <span class="hljs-keyword">and</span> a.Salary &lt; b.Salary</code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> a.NAME <span class="hljs-keyword">AS</span> Employee
<span class="hljs-keyword">FROM</span> Employee <span class="hljs-keyword">AS</span> a <span class="hljs-keyword">JOIN</span> Employee <span class="hljs-keyword">AS</span> b
     <span class="hljs-keyword">ON</span> a.ManagerId = b.Id
     <span class="hljs-keyword">AND</span> a.Salary &gt; b.Salary
;</code></pre>
<h4 id="182-查找重复的电子邮箱"><a href="#182-查找重复的电子邮箱" class="headerlink" title="182 查找重复的电子邮箱"></a>182 查找重复的电子邮箱</h4><h5 id="题目-3"><a href="#题目-3" class="headerlink" title="题目"></a>题目</h5><p>编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。</p>
<pre><code class="hljs sql">+<span class="hljs-comment">----+---------+</span>
| Id | Email   |
+<span class="hljs-comment">----+---------+</span>
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+<span class="hljs-comment">----+---------+</span></code></pre>
<h5 id="解析-3"><a href="#解析-3" class="headerlink" title="解析"></a>解析</h5><p>利用group by分组，count函数统计，having过滤</p>
<h5 id="代码-3"><a href="#代码-3" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">select</span> Email
<span class="hljs-keyword">from</span> Person
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> Email
<span class="hljs-keyword">having</span> <span class="hljs-keyword">count</span>(*) &gt; <span class="hljs-number">1</span></code></pre>
<h4 id="183-从不订购的客户"><a href="#183-从不订购的客户" class="headerlink" title="183 从不订购的客户"></a>183 从不订购的客户</h4><h5 id="题目-4"><a href="#题目-4" class="headerlink" title="题目"></a>题目</h5><p>某网站包含两个表，<code>Customers</code> 表和 <code>Orders</code> 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。</p>
<p>Customers 表：</p>
<pre><code class="hljs asciidoc"><span class="hljs-code">+----+</span>-------+
| Id | Name  |
<span class="hljs-code">+----+</span>-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
<span class="hljs-code">+----+</span>-------+</code></pre>
<p>Orders 表：</p>
<pre><code class="hljs asciidoc"><span class="hljs-code">+----+</span>------------+
| Id | CustomerId |
<span class="hljs-code">+----+</span>------------+
| 1  | 3          |
| 2  | 1          |
<span class="hljs-code">+----+</span>------------+</code></pre>
<h5 id="解析-4"><a href="#解析-4" class="headerlink" title="解析"></a>解析</h5><p>利用not in反向查出不在订货表的客户</p>
<h5 id="代码-4"><a href="#代码-4" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">Name</span> <span class="hljs-keyword">as</span> Customers
<span class="hljs-keyword">from</span> Customers
<span class="hljs-keyword">where</span> <span class="hljs-keyword">Id</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span>(
    <span class="hljs-keyword">select</span> CustomerId
    <span class="hljs-keyword">from</span> Orders
)</code></pre>
<h4 id="196-删除重复的电子邮箱"><a href="#196-删除重复的电子邮箱" class="headerlink" title="196 删除重复的电子邮箱"></a>196 删除重复的电子邮箱</h4><h5 id="题目-5"><a href="#题目-5" class="headerlink" title="题目"></a>题目</h5><p>编写一个 SQL 查询，来删除 <code>Person</code> 表中所有重复的电子邮箱，重复的邮箱里只保留 <strong>Id</strong> <em>最小</em> 的那个。</p>
<pre><code class="hljs asciidoc"><span class="hljs-code">+----+</span>------------------+
| Id | Email            |
<span class="hljs-code">+----+</span>------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
<span class="hljs-code">+----+</span>------------------+
Id 是这个表的主键。</code></pre>
<h5 id="解析-5"><a href="#解析-5" class="headerlink" title="解析"></a>解析</h5><p>联合查询</p>
<h5 id="代码-5"><a href="#代码-5" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">DELETE</span> p1 
<span class="hljs-keyword">FROM</span> Person p1, Person p2
<span class="hljs-keyword">WHERE</span> p1.Email = p2.Email <span class="hljs-keyword">AND</span> p1.Id &gt; p2.Id</code></pre>
<h4 id="197-上升的温度"><a href="#197-上升的温度" class="headerlink" title="197 上升的温度"></a>197 上升的温度</h4><h5 id="题目-6"><a href="#题目-6" class="headerlink" title="题目"></a>题目</h5><p>给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。</p>
<pre><code class="hljs angelscript">+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
|       <span class="hljs-number">1</span> |       <span class="hljs-number">2015</span><span class="hljs-number">-01</span><span class="hljs-number">-01</span> |               <span class="hljs-number">10</span> |
|       <span class="hljs-number">2</span> |       <span class="hljs-number">2015</span><span class="hljs-number">-01</span><span class="hljs-number">-02</span> |               <span class="hljs-number">25</span> |
|       <span class="hljs-number">3</span> |       <span class="hljs-number">2015</span><span class="hljs-number">-01</span><span class="hljs-number">-03</span> |               <span class="hljs-number">20</span> |
|       <span class="hljs-number">4</span> |       <span class="hljs-number">2015</span><span class="hljs-number">-01</span><span class="hljs-number">-04</span> |               <span class="hljs-number">30</span> |
+---------+------------------+------------------+</code></pre>
<h5 id="解析-6"><a href="#解析-6" class="headerlink" title="解析"></a>解析</h5><p>利用DATEDIFF比较日期</p>
<h5 id="代码-6"><a href="#代码-6" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> w2.Id
<span class="hljs-keyword">FROM</span> Weather w1, Weather w2
<span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">DATEDIFF</span>(w2.RecordDate, w1.RecordDate) = <span class="hljs-number">1</span>
<span class="hljs-keyword">AND</span> w1.Temperature &lt; w2.Temperature</code></pre>
<h4 id="595-大的国家"><a href="#595-大的国家" class="headerlink" title="595 大的国家"></a>595 大的国家</h4><h5 id="题目-7"><a href="#题目-7" class="headerlink" title="题目"></a>题目</h5><p>这里有张 <code>World</code> 表</p>
<pre><code class="hljs sql">+<span class="hljs-comment">-----------------+------------+------------+--------------+---------------+</span>
| name            | continent  | area       | population   | gdp           |
+<span class="hljs-comment">-----------------+------------+------------+--------------+---------------+</span>
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+<span class="hljs-comment">-----------------+------------+------------+--------------+---------------+</span></code></pre>
<p>如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。</p>
<p>编写一个SQL查询，输出表中所有大国家的名称、人口和面积。</p>
<h5 id="解析-7"><a href="#解析-7" class="headerlink" title="解析"></a>解析</h5><p>or逻辑</p>
<h5 id="代码-7"><a href="#代码-7" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">name</span>, population, area
<span class="hljs-keyword">from</span> World
<span class="hljs-keyword">where</span> area &gt; <span class="hljs-number">3000000</span> <span class="hljs-keyword">or</span> population &gt; <span class="hljs-number">25000000</span></code></pre>
<h4 id="596-超过5名学生的课程"><a href="#596-超过5名学生的课程" class="headerlink" title="596 超过5名学生的课程"></a>596 超过5名学生的课程</h4><h5 id="题目-8"><a href="#题目-8" class="headerlink" title="题目"></a>题目</h5><p>有一个<code>courses</code> 表 ，有: <strong>student (学生)</strong> 和 <strong>class (课程)</strong>。</p>
<p>请列出所有超过或等于5名学生的课。</p>
<pre><code class="hljs gherkin">+---------+------------+
|<span class="hljs-string"> student </span>|<span class="hljs-string"> class      </span>|
+---------+------------+
|<span class="hljs-string"> A       </span>|<span class="hljs-string"> Math       </span>|
|<span class="hljs-string"> B       </span>|<span class="hljs-string"> English    </span>|
|<span class="hljs-string"> C       </span>|<span class="hljs-string"> Math       </span>|
|<span class="hljs-string"> D       </span>|<span class="hljs-string"> Biology    </span>|
|<span class="hljs-string"> E       </span>|<span class="hljs-string"> Math       </span>|
|<span class="hljs-string"> F       </span>|<span class="hljs-string"> Computer   </span>|
|<span class="hljs-string"> G       </span>|<span class="hljs-string"> Math       </span>|
|<span class="hljs-string"> H       </span>|<span class="hljs-string"> Math       </span>|
|<span class="hljs-string"> I       </span>|<span class="hljs-string"> Math       </span>|
+---------+------------+</code></pre>
<h5 id="解析-8"><a href="#解析-8" class="headerlink" title="解析"></a>解析</h5><p>利用group by分组，having过滤，count(DISTINCT student)统计不重复的学生数</p>
<h5 id="代码-8"><a href="#代码-8" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">class</span>
<span class="hljs-keyword">from</span> courses
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">class</span>
<span class="hljs-keyword">having</span> <span class="hljs-keyword">count</span>(<span class="hljs-keyword">DISTINCT</span> student) &gt;= <span class="hljs-number">5</span></code></pre>
<h4 id="620-有趣的电影"><a href="#620-有趣的电影" class="headerlink" title="620 有趣的电影"></a>620 有趣的电影</h4><h5 id="题目-9"><a href="#题目-9" class="headerlink" title="题目"></a>题目</h5><p>某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。</p>
<p>作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。</p>
<p>例如，下表 <code>cinema</code>:</p>
<pre><code class="hljs gherkin">+---------+-----------+--------------+-----------+
|<span class="hljs-string">   id    </span>|<span class="hljs-string"> movie     </span>|<span class="hljs-string">  description </span>|<span class="hljs-string">  rating   </span>|
+---------+-----------+--------------+-----------+
|<span class="hljs-string">   1     </span>|<span class="hljs-string"> War       </span>|<span class="hljs-string">   great 3D   </span>|<span class="hljs-string">   8.9     </span>|
|<span class="hljs-string">   2     </span>|<span class="hljs-string"> Science   </span>|<span class="hljs-string">   fiction    </span>|<span class="hljs-string">   8.5     </span>|
|<span class="hljs-string">   3     </span>|<span class="hljs-string"> irish     </span>|<span class="hljs-string">   boring     </span>|<span class="hljs-string">   6.2     </span>|
|<span class="hljs-string">   4     </span>|<span class="hljs-string"> Ice song  </span>|<span class="hljs-string">   Fantacy    </span>|<span class="hljs-string">   8.6     </span>|
|<span class="hljs-string">   5     </span>|<span class="hljs-string"> House card</span>|<span class="hljs-string">   Interesting</span>|<span class="hljs-string">   9.1     </span>|
+---------+-----------+--------------+-----------+</code></pre>
<h5 id="解析-9"><a href="#解析-9" class="headerlink" title="解析"></a>解析</h5><p>利用not in去掉boring，order by排序</p>
<h5 id="代码-9"><a href="#代码-9" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">select</span> *
<span class="hljs-keyword">from</span> cinema
<span class="hljs-keyword">where</span> description != <span class="hljs-string">'boring'</span>
<span class="hljs-keyword">and</span> <span class="hljs-keyword">id</span> % <span class="hljs-number">2</span> = <span class="hljs-number">1</span>
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> rating <span class="hljs-keyword">DESC</span></code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> *
<span class="hljs-keyword">from</span> cinema
<span class="hljs-keyword">where</span> description &lt;&gt; <span class="hljs-string">'boring'</span>
<span class="hljs-keyword">and</span> <span class="hljs-keyword">mod</span>(<span class="hljs-keyword">id</span>, <span class="hljs-number">2</span>) = <span class="hljs-number">1</span>
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> rating <span class="hljs-keyword">DESC</span></code></pre>
<h4 id="627-交换性别"><a href="#627-交换性别" class="headerlink" title="627 交换性别"></a>627 交换性别</h4><h5 id="题目-10"><a href="#题目-10" class="headerlink" title="题目"></a>题目</h5><p>给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。</p>
<p>注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。</p>
<p>例如：</p>
<div class="table-container">
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>sex</th>
<th>salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>A</td>
<td>m</td>
<td>2500</td>
</tr>
<tr>
<td>2</td>
<td>B</td>
<td>f</td>
<td>1500</td>
</tr>
<tr>
<td>3</td>
<td>C</td>
<td>m</td>
<td>5500</td>
</tr>
<tr>
<td>4</td>
<td>D</td>
<td>f</td>
<td>500</td>
</tr>
</tbody>
</table>
</div>
<h5 id="解析-10"><a href="#解析-10" class="headerlink" title="解析"></a>解析</h5><p>利用case…when…then；或IF（condition，result1，result2）三元表达式</p>
<h5 id="代码-10"><a href="#代码-10" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">UPDATE</span> salary
<span class="hljs-keyword">SET</span>
    sex = <span class="hljs-keyword">CASE</span> sex
        <span class="hljs-keyword">WHEN</span> <span class="hljs-string">'m'</span> <span class="hljs-keyword">THEN</span> <span class="hljs-string">'f'</span>
        <span class="hljs-keyword">ELSE</span> <span class="hljs-string">'m'</span>
    <span class="hljs-keyword">END</span>;</code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">UPDATE</span> salary <span class="hljs-keyword">set</span> sex = <span class="hljs-keyword">IF</span>(sex=<span class="hljs-string">'f'</span>, <span class="hljs-string">'m'</span>, <span class="hljs-string">'f'</span>);</code></pre>
<h4 id="1179-重新格式化部门表"><a href="#1179-重新格式化部门表" class="headerlink" title="1179 重新格式化部门表"></a>1179 重新格式化部门表</h4><h5 id="题目-11"><a href="#题目-11" class="headerlink" title="题目"></a>题目</h5><p>部门表 Department：</p>
<pre><code class="hljs asciidoc"><span class="hljs-code">+---------------+</span>---------+
| Column Name   | Type    |
<span class="hljs-code">+---------------+</span>---------+
| id            | int     |
| revenue       | int     |
| month         | varchar |
<span class="hljs-code">+---------------+</span>---------+</code></pre>
<p>(id, month) 是表的联合主键。<br>这个表格有关于每个部门每月收入的信息。<br>月份（month）可以取下列值 [“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”]。</p>
<p>编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。</p>
<p>查询结果格式如下面的示例所示：</p>
<p>Department 表：</p>
<pre><code class="hljs gherkin">+------+---------+-------+
|<span class="hljs-string"> id   </span>|<span class="hljs-string"> revenue </span>|<span class="hljs-string"> month </span>|
+------+---------+-------+
|<span class="hljs-string"> 1    </span>|<span class="hljs-string"> 8000    </span>|<span class="hljs-string"> Jan   </span>|
|<span class="hljs-string"> 2    </span>|<span class="hljs-string"> 9000    </span>|<span class="hljs-string"> Jan   </span>|
|<span class="hljs-string"> 3    </span>|<span class="hljs-string"> 10000   </span>|<span class="hljs-string"> Feb   </span>|
|<span class="hljs-string"> 1    </span>|<span class="hljs-string"> 7000    </span>|<span class="hljs-string"> Feb   </span>|
|<span class="hljs-string"> 1    </span>|<span class="hljs-string"> 6000    </span>|<span class="hljs-string"> Mar   </span>|
+------+---------+-------+</code></pre>
<p>查询得到的结果表：</p>
<pre><code class="hljs gherkin">+------+-------------+-------------+-------------+-----+-------------+
|<span class="hljs-string"> id   </span>|<span class="hljs-string"> Jan_Revenue </span>|<span class="hljs-string"> Feb_Revenue </span>|<span class="hljs-string"> Mar_Revenue </span>|<span class="hljs-string"> ... </span>|<span class="hljs-string"> Dec_Revenue </span>|
+------+-------------+-------------+-------------+-----+-------------+
|<span class="hljs-string"> 1    </span>|<span class="hljs-string"> 8000        </span>|<span class="hljs-string"> 7000        </span>|<span class="hljs-string"> 6000        </span>|<span class="hljs-string"> ... </span>|<span class="hljs-string"> null        </span>|
|<span class="hljs-string"> 2    </span>|<span class="hljs-string"> 9000        </span>|<span class="hljs-string"> null        </span>|<span class="hljs-string"> null        </span>|<span class="hljs-string"> ... </span>|<span class="hljs-string"> null        </span>|
|<span class="hljs-string"> 3    </span>|<span class="hljs-string"> null        </span>|<span class="hljs-string"> 10000       </span>|<span class="hljs-string"> null        </span>|<span class="hljs-string"> ... </span>|<span class="hljs-string"> null        </span>|
+------+-------------+-------------+-------------+-----+-------------+</code></pre>
<p>注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。</p>
<h5 id="解析-11"><a href="#解析-11" class="headerlink" title="解析"></a>解析</h5><p>行转列，利用CASE…WHEN..THEN选择特定月份，SUM统计收入</p>
<h5 id="代码-11"><a href="#代码-11" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">id</span>, 
<span class="hljs-keyword">SUM</span>(<span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">month</span>=<span class="hljs-string">'Jan'</span> <span class="hljs-keyword">THEN</span> revenue <span class="hljs-keyword">END</span>) <span class="hljs-keyword">AS</span> Jan_Revenue,
<span class="hljs-keyword">SUM</span>(<span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">month</span>=<span class="hljs-string">'Feb'</span> <span class="hljs-keyword">THEN</span> revenue <span class="hljs-keyword">END</span>) <span class="hljs-keyword">AS</span> Feb_Revenue,
<span class="hljs-keyword">SUM</span>(<span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">month</span>=<span class="hljs-string">'Mar'</span> <span class="hljs-keyword">THEN</span> revenue <span class="hljs-keyword">END</span>) <span class="hljs-keyword">AS</span> Mar_Revenue,
<span class="hljs-keyword">SUM</span>(<span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">month</span>=<span class="hljs-string">'Apr'</span> <span class="hljs-keyword">THEN</span> revenue <span class="hljs-keyword">END</span>) <span class="hljs-keyword">AS</span> Apr_Revenue,
<span class="hljs-keyword">SUM</span>(<span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">month</span>=<span class="hljs-string">'May'</span> <span class="hljs-keyword">THEN</span> revenue <span class="hljs-keyword">END</span>) <span class="hljs-keyword">AS</span> May_Revenue,
<span class="hljs-keyword">SUM</span>(<span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">month</span>=<span class="hljs-string">'Jun'</span> <span class="hljs-keyword">THEN</span> revenue <span class="hljs-keyword">END</span>) <span class="hljs-keyword">AS</span> Jun_Revenue,
<span class="hljs-keyword">SUM</span>(<span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">month</span>=<span class="hljs-string">'Jul'</span> <span class="hljs-keyword">THEN</span> revenue <span class="hljs-keyword">END</span>) <span class="hljs-keyword">AS</span> Jul_Revenue,
<span class="hljs-keyword">SUM</span>(<span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">month</span>=<span class="hljs-string">'Aug'</span> <span class="hljs-keyword">THEN</span> revenue <span class="hljs-keyword">END</span>) <span class="hljs-keyword">AS</span> Aug_Revenue,
<span class="hljs-keyword">SUM</span>(<span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">month</span>=<span class="hljs-string">'Sep'</span> <span class="hljs-keyword">THEN</span> revenue <span class="hljs-keyword">END</span>) <span class="hljs-keyword">AS</span> Sep_Revenue,
<span class="hljs-keyword">SUM</span>(<span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">month</span>=<span class="hljs-string">'Oct'</span> <span class="hljs-keyword">THEN</span> revenue <span class="hljs-keyword">END</span>) <span class="hljs-keyword">AS</span> Oct_Revenue,
<span class="hljs-keyword">SUM</span>(<span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">month</span>=<span class="hljs-string">'Nov'</span> <span class="hljs-keyword">THEN</span> revenue <span class="hljs-keyword">END</span>) <span class="hljs-keyword">AS</span> Nov_Revenue,
<span class="hljs-keyword">SUM</span>(<span class="hljs-keyword">CASE</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">month</span>=<span class="hljs-string">'Dec'</span> <span class="hljs-keyword">THEN</span> revenue <span class="hljs-keyword">END</span>) <span class="hljs-keyword">AS</span> Dec_Revenue
<span class="hljs-keyword">FROM</span> department
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> <span class="hljs-keyword">id</span>
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> <span class="hljs-keyword">id</span>;</code></pre>
<h3 id="中等题-6道"><a href="#中等题-6道" class="headerlink" title="中等题 6道"></a>中等题 6道</h3><h4 id="177-第N高的薪水"><a href="#177-第N高的薪水" class="headerlink" title="177 第N高的薪水"></a>177 第N高的薪水</h4><h5 id="题目-12"><a href="#题目-12" class="headerlink" title="题目"></a>题目</h5><p>编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。</p>
<pre><code class="hljs asciidoc"><span class="hljs-code">+----+</span>--------+
| Id | Salary |
<span class="hljs-code">+----+</span>--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
<span class="hljs-code">+----+</span>--------+</code></pre>
<p>例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。</p>
<h5 id="解析-12"><a href="#解析-12" class="headerlink" title="解析"></a>解析</h5><p>按Salary用group by聚合分组，再降序排序，利用limit限制读取数，offset设置偏移量</p>
<h5 id="代码-12"><a href="#代码-12" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">FUNCTION</span> getNthHighestSalary(N <span class="hljs-built_in">INT</span>) <span class="hljs-keyword">RETURNS</span> <span class="hljs-built_in">INT</span>
<span class="hljs-keyword">BEGIN</span>
    <span class="hljs-keyword">set</span> N := N<span class="hljs-number">-1</span>;
  RETURN (
      <span class="hljs-keyword">select</span> Salary
      <span class="hljs-keyword">from</span> Employee
      <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> Salary
      <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> Salary <span class="hljs-keyword">DESC</span>
      <span class="hljs-keyword">limit</span> <span class="hljs-number">1</span> <span class="hljs-keyword">offset</span> N
  );
<span class="hljs-keyword">END</span></code></pre>
<h4 id="178-分数排名"><a href="#178-分数排名" class="headerlink" title="178 分数排名"></a>178 分数排名</h4><h5 id="题目-13"><a href="#题目-13" class="headerlink" title="题目"></a>题目</h5><p>编写一个 SQL 查询来实现分数排名。</p>
<p>如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。</p>
<pre><code class="hljs gherkin">+----+-------+
|<span class="hljs-string"> Id </span>|<span class="hljs-string"> Score </span>|
+----+-------+
|<span class="hljs-string"> 1  </span>|<span class="hljs-string"> 3.50  </span>|
|<span class="hljs-string"> 2  </span>|<span class="hljs-string"> 3.65  </span>|
|<span class="hljs-string"> 3  </span>|<span class="hljs-string"> 4.00  </span>|
|<span class="hljs-string"> 4  </span>|<span class="hljs-string"> 3.85  </span>|
|<span class="hljs-string"> 5  </span>|<span class="hljs-string"> 4.00  </span>|
|<span class="hljs-string"> 6  </span>|<span class="hljs-string"> 3.65  </span>|
+----+-------+</code></pre>
<h5 id="解析-13"><a href="#解析-13" class="headerlink" title="解析"></a>解析</h5><p>利用distinct去重，之后统计b中分数大于等于a中的</p>
<p>利用dense_rank函数</p>
<h5 id="代码-13"><a href="#代码-13" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">select</span> a.Score <span class="hljs-keyword">as</span> Score, (
    <span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(<span class="hljs-keyword">distinct</span> b.Score) 
    <span class="hljs-keyword">from</span> Scores b 
    <span class="hljs-keyword">where</span> b.Score &gt;= a.Score) <span class="hljs-keyword">as</span> <span class="hljs-string">'Rank'</span>
<span class="hljs-keyword">from</span> Scores a
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> Score <span class="hljs-keyword">DESC</span></code></pre>
<pre><code class="hljs sql"><span class="hljs-keyword">select</span> Score, <span class="hljs-keyword">dense_rank</span>() <span class="hljs-keyword">over</span> (<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> Score <span class="hljs-keyword">desc</span>)  <span class="hljs-keyword">as</span> <span class="hljs-string">`Rank`</span>
<span class="hljs-keyword">from</span> Scores;</code></pre>
<h4 id="180-连续出现的数字"><a href="#180-连续出现的数字" class="headerlink" title="180 连续出现的数字"></a>180 连续出现的数字</h4><h5 id="题目-14"><a href="#题目-14" class="headerlink" title="题目"></a>题目</h5><p>编写一个 SQL 查询，查找所有至少<strong>连续出现三次</strong>的数字。</p>
<pre><code class="hljs gherkin">+----+-----+
|<span class="hljs-string"> Id </span>|<span class="hljs-string"> Num </span>|
+----+-----+
|<span class="hljs-string"> 1  </span>|<span class="hljs-string">  1  </span>|
|<span class="hljs-string"> 2  </span>|<span class="hljs-string">  1  </span>|
|<span class="hljs-string"> 3  </span>|<span class="hljs-string">  1  </span>|
|<span class="hljs-string"> 4  </span>|<span class="hljs-string">  2  </span>|
|<span class="hljs-string"> 5  </span>|<span class="hljs-string">  1  </span>|
|<span class="hljs-string"> 6  </span>|<span class="hljs-string">  2  </span>|
|<span class="hljs-string"> 7  </span>|<span class="hljs-string">  2  </span>|
+----+-----+</code></pre>
<h5 id="解析-14"><a href="#解析-14" class="headerlink" title="解析"></a>解析</h5><p>自连接</p>
<h5 id="代码-14"><a href="#代码-14" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">DISTINCT</span>
    l1.Num <span class="hljs-keyword">AS</span> ConsecutiveNums
<span class="hljs-keyword">FROM</span>
    <span class="hljs-keyword">Logs</span> l1,
    <span class="hljs-keyword">Logs</span> l2,
    <span class="hljs-keyword">Logs</span> l3
<span class="hljs-keyword">WHERE</span>
    l1.Id = l2.Id - <span class="hljs-number">1</span>
    <span class="hljs-keyword">AND</span> l2.Id = l3.Id - <span class="hljs-number">1</span>
    <span class="hljs-keyword">AND</span> l1.Num = l2.Num
    <span class="hljs-keyword">AND</span> l2.Num = l3.Num
;</code></pre>
<h4 id="184-部门工资最高的员工"><a href="#184-部门工资最高的员工" class="headerlink" title="184 部门工资最高的员工"></a>184 部门工资最高的员工</h4><h5 id="题目-15"><a href="#题目-15" class="headerlink" title="题目"></a>题目</h5><p>Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。</p>
<pre><code class="hljs gherkin">+----+-------+--------+--------------+
|<span class="hljs-string"> Id </span>|<span class="hljs-string"> Name  </span>|<span class="hljs-string"> Salary </span>|<span class="hljs-string"> DepartmentId </span>|
+----+-------+--------+--------------+
|<span class="hljs-string"> 1  </span>|<span class="hljs-string"> Joe   </span>|<span class="hljs-string"> 70000  </span>|<span class="hljs-string"> 1            </span>|
|<span class="hljs-string"> 2  </span>|<span class="hljs-string"> Jim   </span>|<span class="hljs-string"> 90000  </span>|<span class="hljs-string"> 1            </span>|
|<span class="hljs-string"> 3  </span>|<span class="hljs-string"> Henry </span>|<span class="hljs-string"> 80000  </span>|<span class="hljs-string"> 2            </span>|
|<span class="hljs-string"> 4  </span>|<span class="hljs-string"> Sam   </span>|<span class="hljs-string"> 60000  </span>|<span class="hljs-string"> 2            </span>|
|<span class="hljs-string"> 5  </span>|<span class="hljs-string"> Max   </span>|<span class="hljs-string"> 90000  </span>|<span class="hljs-string"> 1            </span>|
+----+-------+--------+--------------+</code></pre>
<p>Department 表包含公司所有部门的信息。</p>
<pre><code class="hljs asciidoc"><span class="hljs-code">+----+</span>----------+
| Id | Name     |
<span class="hljs-code">+----+</span>----------+
| 1  | IT       |
| 2  | Sales    |
<span class="hljs-code">+----+</span>----------+</code></pre>
<p>编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您</p>
<pre><code class="hljs asciidoc">的 SQL 查询应返回以下行（行的顺序无关紧要）。

<span class="hljs-code">+------------+</span>----------<span class="hljs-code">+--------+</span>
| Department | Employee | Salary |
<span class="hljs-code">+------------+</span>----------<span class="hljs-code">+--------+</span>
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
<span class="hljs-code">+------------+</span>----------<span class="hljs-code">+--------+</span></code></pre>
<h5 id="解析-15"><a href="#解析-15" class="headerlink" title="解析"></a>解析</h5><p>先内连接生成结果表，再利用in过滤出工资最高的员工</p>
<h5 id="代码-15"><a href="#代码-15" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span>
    Department.name <span class="hljs-keyword">AS</span> <span class="hljs-string">'Department'</span>,
    Employee.name <span class="hljs-keyword">AS</span> <span class="hljs-string">'Employee'</span>,
    Salary
<span class="hljs-keyword">FROM</span>
    Employee
        <span class="hljs-keyword">JOIN</span>
    Department <span class="hljs-keyword">ON</span> Employee.DepartmentId = Department.Id
<span class="hljs-keyword">WHERE</span>
    (Employee.DepartmentId , Salary) <span class="hljs-keyword">IN</span>
    (   <span class="hljs-keyword">SELECT</span>
            DepartmentId, <span class="hljs-keyword">MAX</span>(Salary)
        <span class="hljs-keyword">FROM</span>
            Employee
        <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> DepartmentId
	)
;</code></pre>
<h4 id="185-部门工资前三高的所有员工"><a href="#185-部门工资前三高的所有员工" class="headerlink" title="185 部门工资前三高的所有员工"></a>185 部门工资前三高的所有员工</h4><h5 id="题目-16"><a href="#题目-16" class="headerlink" title="题目"></a>题目</h5><p>Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。</p>
<pre><code class="hljs gherkin">+----+-------+--------+--------------+
|<span class="hljs-string"> Id </span>|<span class="hljs-string"> Name  </span>|<span class="hljs-string"> Salary </span>|<span class="hljs-string"> DepartmentId </span>|
+----+-------+--------+--------------+
|<span class="hljs-string"> 1  </span>|<span class="hljs-string"> Joe   </span>|<span class="hljs-string"> 85000  </span>|<span class="hljs-string"> 1            </span>|
|<span class="hljs-string"> 2  </span>|<span class="hljs-string"> Henry </span>|<span class="hljs-string"> 80000  </span>|<span class="hljs-string"> 2            </span>|
|<span class="hljs-string"> 3  </span>|<span class="hljs-string"> Sam   </span>|<span class="hljs-string"> 60000  </span>|<span class="hljs-string"> 2            </span>|
|<span class="hljs-string"> 4  </span>|<span class="hljs-string"> Max   </span>|<span class="hljs-string"> 90000  </span>|<span class="hljs-string"> 1            </span>|
|<span class="hljs-string"> 5  </span>|<span class="hljs-string"> Janet </span>|<span class="hljs-string"> 69000  </span>|<span class="hljs-string"> 1            </span>|
|<span class="hljs-string"> 6  </span>|<span class="hljs-string"> Randy </span>|<span class="hljs-string"> 85000  </span>|<span class="hljs-string"> 1            </span>|
|<span class="hljs-string"> 7  </span>|<span class="hljs-string"> Will  </span>|<span class="hljs-string"> 70000  </span>|<span class="hljs-string"> 1            </span>|
+----+-------+--------+--------------+</code></pre>
<p>Department 表包含公司所有部门的信息。</p>
<pre><code class="hljs asciidoc"><span class="hljs-code">+----+</span>----------+
| Id | Name     |
<span class="hljs-code">+----+</span>----------+
| 1  | IT       |
| 2  | Sales    |
<span class="hljs-code">+----+</span>----------+</code></pre>
<p>编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：</p>
<pre><code class="hljs gherkin">+------------+----------+--------+
|<span class="hljs-string"> Department </span>|<span class="hljs-string"> Employee </span>|<span class="hljs-string"> Salary </span>|
+------------+----------+--------+
|<span class="hljs-string"> IT         </span>|<span class="hljs-string"> Max      </span>|<span class="hljs-string"> 90000  </span>|
|<span class="hljs-string"> IT         </span>|<span class="hljs-string"> Randy    </span>|<span class="hljs-string"> 85000  </span>|
|<span class="hljs-string"> IT         </span>|<span class="hljs-string"> Joe      </span>|<span class="hljs-string"> 85000  </span>|
|<span class="hljs-string"> IT         </span>|<span class="hljs-string"> Will     </span>|<span class="hljs-string"> 70000  </span>|
|<span class="hljs-string"> Sales      </span>|<span class="hljs-string"> Henry    </span>|<span class="hljs-string"> 80000  </span>|
|<span class="hljs-string"> Sales      </span>|<span class="hljs-string"> Sam      </span>|<span class="hljs-string"> 60000  </span>|
+------------+----------+--------+</code></pre>
<h5 id="解析-16"><a href="#解析-16" class="headerlink" title="解析"></a>解析</h5><p>内连接生成结果表，两个员工表做比较，e2表中工资大于e1中少于等于2个的为前3工资高员工</p>
<h5 id="代码-16"><a href="#代码-16" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span>
    d.Name <span class="hljs-keyword">AS</span> <span class="hljs-string">'Department'</span>, e1.Name <span class="hljs-keyword">AS</span> <span class="hljs-string">'Employee'</span>, e1.Salary
<span class="hljs-keyword">FROM</span>
    Employee e1
        <span class="hljs-keyword">JOIN</span>
    Department d <span class="hljs-keyword">ON</span> e1.DepartmentId = d.Id
<span class="hljs-keyword">WHERE</span>
    <span class="hljs-number">3</span> &gt; (<span class="hljs-keyword">SELECT</span>
            <span class="hljs-keyword">COUNT</span>(<span class="hljs-keyword">DISTINCT</span> e2.Salary)
        <span class="hljs-keyword">FROM</span>
            Employee e2
        <span class="hljs-keyword">WHERE</span>
            e2.Salary &gt; e1.Salary
                <span class="hljs-keyword">AND</span> e1.DepartmentId = e2.DepartmentId
        )
;</code></pre>
<h4 id="626-换座位"><a href="#626-换座位" class="headerlink" title="626 换座位"></a>626 换座位</h4><h5 id="题目-17"><a href="#题目-17" class="headerlink" title="题目"></a>题目</h5><p>小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。</p>
<p>其中纵列的 id 是连续递增的</p>
<p>小美想改变相邻俩学生的座位。</p>
<p>你能不能帮她写一个 SQL query 来输出小美想要的结果呢？</p>
<pre><code class="hljs asciidoc"><span class="hljs-code">+---------+</span>---------+
|    id   | student |
<span class="hljs-code">+---------+</span>---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
<span class="hljs-code">+---------+</span>---------+</code></pre>
<h5 id="解析-17"><a href="#解析-17" class="headerlink" title="解析"></a>解析</h5><p>判断id奇偶和是否最后一位奇数进行赋值，统计id为第几位利用counts</p>
<h5 id="代码-17"><a href="#代码-17" class="headerlink" title="代码"></a>代码</h5><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span>
    (<span class="hljs-keyword">CASE</span>
        <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">MOD</span>(<span class="hljs-keyword">id</span>, <span class="hljs-number">2</span>) != <span class="hljs-number">0</span> <span class="hljs-keyword">AND</span> counts != <span class="hljs-keyword">id</span> <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">id</span> + <span class="hljs-number">1</span>
        <span class="hljs-keyword">WHEN</span> <span class="hljs-keyword">MOD</span>(<span class="hljs-keyword">id</span>, <span class="hljs-number">2</span>) != <span class="hljs-number">0</span> <span class="hljs-keyword">AND</span> counts = <span class="hljs-keyword">id</span> <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">id</span>
        <span class="hljs-keyword">ELSE</span> <span class="hljs-keyword">id</span> - <span class="hljs-number">1</span>
    <span class="hljs-keyword">END</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">id</span>,
    student
<span class="hljs-keyword">FROM</span>
    seat,
    (<span class="hljs-keyword">SELECT</span>
        <span class="hljs-keyword">COUNT</span>(*) <span class="hljs-keyword">AS</span> counts
    <span class="hljs-keyword">FROM</span>
        seat) <span class="hljs-keyword">AS</span> seat_counts
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> <span class="hljs-keyword">id</span> <span class="hljs-keyword">ASC</span>;</code></pre>

            </article>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                  <div class="post-meta mr-3">
                    <i class="iconfont icon-category"></i>
                    
                      <a class="hover-with-bg" href="/michuanblog/categories/%E9%9D%A2%E8%AF%95/">面试</a>
                    
                  </div>
                
                
                  <div class="post-meta">
                    <i class="iconfont icon-tags"></i>
                    
                      <a class="hover-with-bg" href="/michuanblog/tags/%E9%9D%A2%E8%AF%95/">面试</a>
                    
                      <a class="hover-with-bg" href="/michuanblog/tags/MySQL/">MySQL</a>
                    
                      <a class="hover-with-bg" href="/michuanblog/tags/LeetCode/">LeetCode</a>
                    
                      <a class="hover-with-bg" href="/michuanblog/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a>
                    
                  </div>
                
              </div>
              
                <p class="note note-warning">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-sa/4.0/deed.zh" target="_blank" rel="nofollow noopener noopener">CC BY-SA 4.0 协议</a> ，转载请注明出处！</p>
              
              
                <div class="post-prevnext row">
                  <div class="post-prev col-6">
                    
                    
                      <a href="/michuanblog/2020/09/05/lcci-content/">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">lcci_content</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </div>
                  <div class="post-next col-6">
                    
                    
                      <a href="/michuanblog/2020/08/15/Linux%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/">
                        <span class="hidden-mobile">Linux常用命令</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </div>
                </div>
              
            </div>

            
              <!-- Comments -->
              <div class="comments" id="comments">
                
                
  <div id="vcomments"></div>
  <script type="text/javascript">
    function loadValine() {
      addScript('https://cdn.staticfile.org/valine/1.4.14/Valine.min.js', function () {
        new Valine({
          el: "#vcomments",
          app_id: "tKy1BysLG32PuX7L8s0rQi9C-gzGzoHsz",
          app_key: "wlDiPjGWwM0U49QD4CpxH5dz",
          placeholder: "说点什么",
          path: window.location.pathname,
          avatar: "retro",
          meta: ["nick","mail","link"],
          pageSize: "10",
          lang: "zh-CN",
          highlight: false,
          recordIP: false,
          serverURLs: "",
        });
      });
    }
    createObserver(loadValine, 'vcomments');
  </script>
  <noscript>Please enable JavaScript to view the <a href="https://valine.js.org" target="_blank" rel="nofollow noopener noopener">comments
      powered by Valine.</a></noscript>


              </div>
            
          </div>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;目录</p>
  <div id="tocbot"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->


    
  </main>

  
    <a id="scroll-top-button" href="#" role="button">
      <i class="iconfont icon-arrowup" aria-hidden="true"></i>
    </a>
  

  
    <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
  

  
    <!-- APlayer 音乐播放器 -->
    <div id="aplayer"></div>
    <script defer src="https://cdn.staticfile.org/aplayer/1.10.1/APlayer.min.js" ></script>
<link  rel="stylesheet" href="https://cdn.staticfile.org/aplayer/1.10.1/APlayer.min.css" />
<script type="text/javascript">
  var oldLoadAp = window.onload;
  window.onload = function () {
    oldLoadAp && oldLoadAp();

    new APlayer({
      container: document.getElementById('aplayer'),
      fixed: true,
      autoplay: 'false' === 'true',
      loop: 'all',
      order: 'random',
      theme: '#b7daff',
      preload: 'none',
      audio: [{"name":"黄杨扁担","artist":"鹿女王","url":"http://mi_chuan.gitee.io/blog/黄杨扁担.mp3","cover":"http://mi_chuan.gitee.io/blog/黄杨扁担.png"},{"name":"隔世信","artist":"以冬","url":"http://mi_chuan.gitee.io/blog/隔世信.mp3","cover":"http://mi_chuan.gitee.io/blog/隔世信.jpg"}]
    });
  }
</script>

  

  

  <footer class="mt-5">
  <div class="text-center py-3">
    <div>
      <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a>
      <i class="iconfont icon-love"></i>
      <a href="https://github.com/fluid-dev/hexo-theme-fluid" target="_blank" rel="nofollow noopener">
        <span>Fluid</span></a>
    </div>
    

    

    
  </div>
</footer>

<!-- SCRIPTS -->
<script  src="https://cdn.staticfile.org/jquery/3.4.1/jquery.min.js" ></script>
<script  src="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/js/bootstrap.min.js" ></script>
<script  src="/michuanblog/js/debouncer.js" ></script>
<script  src="/michuanblog/js/main.js" ></script>

<!-- Plugins -->


  
    <script  src="/michuanblog/js/lazyload.js" ></script>
  



  <script defer src="https://cdn.staticfile.org/clipboard.js/2.0.6/clipboard.min.js" ></script>
  <script  src="/michuanblog/js/clipboard-use.js" ></script>







  <script  src="https://cdn.staticfile.org/tocbot/4.11.1/tocbot.min.js" ></script>
  <script>
    $(document).ready(function () {
      var boardCtn = $('#board-ctn');
      var boardTop = boardCtn.offset().top;

      tocbot.init({
        tocSelector: '#tocbot',
        contentSelector: 'article.markdown-body',
        headingSelector: 'h1,h2,h3,h4,h5,h6',
        linkClass: 'tocbot-link',
        activeLinkClass: 'tocbot-active-link',
        listClass: 'tocbot-list',
        isCollapsedClass: 'tocbot-is-collapsed',
        collapsibleClass: 'tocbot-is-collapsible',
        collapseDepth: 0,
        scrollSmooth: true,
        headingsOffset: -boardTop
      });
      if ($('.toc-list-item').length > 0) {
        $('#toc').css('visibility', 'visible');
      }
    });
  </script>



  <script  src="https://cdn.staticfile.org/typed.js/2.0.11/typed.min.js" ></script>
  <script>
    var typed = new Typed('#subtitle', {
      strings: [
        '  ',
        "LeetCodede_DataBase&nbsp;",
      ],
      cursorChar: "_",
      typeSpeed: 70,
      loop: false,
    });
    typed.stop();
    $(document).ready(function () {
      $(".typed-cursor").addClass("h2");
      typed.start();
    });
  </script>



  <script  src="https://cdn.staticfile.org/anchor-js/4.2.2/anchor.min.js" ></script>
  <script>
    anchors.options = {
      placement: "right",
      visible: "hover",
      
    };
    var el = "h1,h2,h3,h4,h5,h6".split(",");
    var res = [];
    for (item of el) {
      res.push(".markdown-body > " + item)
    }
    anchors.add(res.join(", "))
  </script>



  <script  src="/michuanblog/js/local-search.js" ></script>
  <script>
    var path = "/michuanblog/local-search.xml";
    var inputArea = document.querySelector("#local-search-input");
    inputArea.onclick = function () {
      searchFunc(path, 'local-search-input', 'local-search-result');
      this.onclick = null
    }
  </script>



  <script  src="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.css" />

  <script>
    $('#post img:not(.no-zoom img, img[no-zoom]), img[zoom]').each(
      function () {
        var element = document.createElement('a');
        $(element).attr('data-fancybox', 'images');
        $(element).attr('href', $(this).attr('src'));
        $(this).wrap(element);
      }
    );
  </script>





  

  
    <!-- MathJax -->
    <script>
      MathJax = {
        tex: {
          inlineMath: [['$', '$'], ['\\(', '\\)']]
        },
        options: {
          renderActions: {
            findScript: [10, doc => {
              document.querySelectorAll('script[type^="math/tex"]').forEach(node => {
                const display = !!node.type.match(/; *mode=display/);
                const math = new doc.options.MathItem(node.textContent, doc.inputJax[0], display);
                const text = document.createTextNode('');
                node.parentNode.replaceChild(text, node);
                math.start = { node: text, delim: '', n: 0 };
                math.end = { node: text, delim: '', n: 0 };
                doc.math.push(math);
              });
            }, '', false],
            insertedScript: [200, () => {
              document.querySelectorAll('mjx-container').forEach(node => {
                let target = node.parentNode;
                if (target.nodeName.toLowerCase() === 'li') {
                  target.parentNode.classList.add('has-jax');
                }
              });
            }, '', false]
          }
        }
      };
    </script>

    <script async src="https://cdn.staticfile.org/mathjax/3.0.5/es5/tex-svg.js" ></script>

  



  
  
    <script>
      !function (e, t, a) {
        function r() {
          for (var e = 0; e < s.length; e++) s[e].alpha <= 0 ? (t.body.removeChild(s[e].el), s.splice(e, 1)) : (s[e].y--, s[e].scale += .004, s[e].alpha -= .013, s[e].el.style.cssText = "left:" + s[e].x + "px;top:" + s[e].y + "px;opacity:" + s[e].alpha + ";transform:scale(" + s[e].scale + "," + s[e].scale + ") rotate(45deg);background:" + s[e].color + ";z-index:99999");
          requestAnimationFrame(r)
        }

        function n() {
          var t = "function" == typeof e.onclick && e.onclick;
          e.onclick = function (e) {
            t && t(), o(e)
          }
        }

        function o(e) {
          var a = t.createElement("div");
          a.className = "heart", s.push({
            el: a,
            x: e.clientX - 5,
            y: e.clientY - 5,
            scale: 1,
            alpha: 1,
            color: c()
          }), t.body.appendChild(a)
        }

        function i(e) {
          var a = t.createElement("style");
          a.type = "text/css";
          try {
            a.appendChild(t.createTextNode(e))
          } catch (t) {
            a.styleSheet.cssText = e
          }
          t.getElementsByTagName("head")[0].appendChild(a)
        }

        function c() {
          return "rgb(" + ~~(255 * Math.random()) + "," + ~~(255 * Math.random()) + "," + ~~(255 * Math.random()) + ")"
        }

        var s = [];
        e.requestAnimationFrame = e.requestAnimationFrame || e.webkitRequestAnimationFrame || e.mozRequestAnimationFrame || e.oRequestAnimationFrame || e.msRequestAnimationFrame || function (e) {
          setTimeout(e, 1e3 / 60)
        }, i(".heart{width: 10px;height: 10px;position: fixed;background: #f00;transform: rotate(45deg);-webkit-transform: rotate(45deg);-moz-transform: rotate(45deg);}.heart:after,.heart:before{content: '';width: inherit;height: inherit;background: inherit;border-radius: 50%;-webkit-border-radius: 50%;-moz-border-radius: 50%;position: fixed;}.heart:after{top: -5px;}.heart:before{left: -5px;}"), n(), r()
      }(window, document);
    </script>
  








  <script  src="https://cdn.staticfile.org/mermaid/8.5.0/mermaid.min.js" ></script>
  <script>
    if (window.mermaid) {
      mermaid.initialize({"theme":"default"});
    }
  </script>







<script src="/michuanblog/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({"log":false,"pluginJsPath":"lib/","pluginModelPath":"assets/","pluginRootPath":"live2dw/","tagMode":false});</script></body>
</html>
